
-- created the type for the rows of the nested table
CREATE OR REPLACE TYPE ContactType AS OBJECT(
	lastName	VARCHAR2(15),
	firstName	VARCHAR2(12),
	relationship	VARCHAR2(15),
	phoneNumber	VARCHAR2(10));
/  

-- create the type for the nested table itself
CREATE OR REPLACE TYPE ContactListType AS TABLE OF ContactType;
/

-- create the type for the primary object table with the nested table type as a column
create or replace type studenttype3 as object(
stuid		varchar2(6),
lastName	varchar2(20),
firstName	varchar2(20),
address		Addresstype,
advid		REF FacultyType2,
credits		number(3),
dateOfBirth	date,
contactList	contactListType)
INSTANTIABLE
NOT FINAL;
/

--create the primary object table, providing storage for the nested table

CREATE TABLE Student3 OF StudentType3(
CONSTRAINT Stu3_stuId_pk PRIMARY KEY (stuId))
object id primary key
NESTED TABLE contactList 
STORE AS contactListStorageTable(
(primary key (nested_table_id, lastName, firstName))
organization index compress)
return as locator
/

 
--insert a Student3 row with an empty contact list

INSERT INTO Student3(stuid, lastname, firstname,credits, contactList)
VALUES ('S999', 'Smith', 'John',0, contactListType());


-- insert two rows into the nested table for the student
INSERT INTO TABLE(SELECT s.contactList
			FROM		Student3 s
			WHERE	s.stuId = 'S999')
VALUES('Smith','Marjorie','mother','2017771234');

INSERT INTO TABLE(SELECT s.contactList
			FROM		Student3 s
			WHERE	s.stuId = 'S999')
VALUES('Smith','Frank','father','9177778888');


--retrieve the student and nested contact list
SELECT s.lastName, s.firstName, c.*
FROM 	Student3 s, TABLE(s.contactList) c
WHERE s.credits <= 60;





